<?php

/**
 * 园区学生成绩查询导出
 * 
 * @version 1.0
 * @date 2013-12-13
 */
class ResultsController extends FController {

    public $user_school_list;
    public $user_subject_list;
    public $user_role_list;
    public $exam_id;
	public function accessRules() {
		$config = array(
		'index' => 1
		);
		return ZRole::auth("school_info", $config);
	}

	public function __common() {
		parent::init();
		if (empty($this->cLoginUser)) {
			$this->redirect($this->createUrl('/site/login'));
        } else {
            $userLoginUser = $this->cLoginUser;
            $this->user_role_list = isset($userLoginUser['cuser']['role_id']) ? $userLoginUser['cuser']['role_id'] : '';
            $this->user_school_list = isset($userLoginUser['cuser']['school']['school_id']) ? $userLoginUser['cuser']['school']['school_id'] : '';
            $this->user_subject_list = isset($userLoginUser['cuser']['school']['subject_id']) ? $userLoginUser['cuser']['school']['subject_id'] : '';
            $zRow = new ZRole();
            //得到学校对应的考试
            $exam_list = $zRow->_getSchoolExam($this->user_school_list);
            if (!empty($exam_list)) {
                $this->exam_id = implode(',', $exam_list);
            }
		}
		$this->commonScript();
		Yii::app()->clientScript->registerCssFile(THEME_CSS_URL . '/module_Sip.css');
	}

	//列表显示
	public function actionIndex() {
		$this->__common();
		$conn = Yii::app()->db;
		$this->title = "园区学生成绩查询导出";
		$limit = 8;
        $where = $qWhere = '';
        //权限判断
        if ($this->user_role_list != 1 && !empty($this->exam_id)) {
        	$where = ' AND "exam_id" IN (' . $this->exam_id . ')';
        	$zExam = new zExam();
        	if(!empty($this->user_school_list)){
            	$qWhere = ' AND S."school_id" IN (' . $this->user_school_list . ')';
        	}
        }
		$date['ztask'] = ZTask::_getExamAll($where);
		$date['school'] = ExamData::_getSchoolList($this->user_school_list);
		$date['subject'] = ZProblemNum::_getAllsubject();
		$date['class'] = ZTask::_getclass();
		$date['disciplin'] = ExamData::_getDisciplin();
		
		$info['exam_id'] = Yii::app()->request->getParam('exam_id');
		$info['school_id'] = Yii::app()->request->getParam('school_id');
		$info['subject_id'] = Yii::app()->request->getParam('subject_id');
		$info['disciplin_id'] = Yii::app()->request->getParam('disciplin_id');
		$info['class_name'] = Yii::app()->request->getParam('class_name');
		$info['nojoinstatus'] = Yii::app()->request->getParam('nojoinstatus');
		$info['import'] = Yii::app()->request->getParam('import');
		$info['disciplin_code'] = Yii::app()->request->getParam('disciplin_code');
		
		$where = '';
		$order = ' ORDER BY s."student_id" ASC ';
		# 条件拼接 #
		if (!empty($info['exam_id'])) {
			$where.= ' AND s."exam_id" = ' . $info['exam_id'];
		}
		if (!empty($info['school_id'])) {
			$where.= ' AND s."school_id" = ' . $info['school_id'];
		}else{
			$where .= $qWhere;
		}
		if (!empty($info['disciplin_id'])) {
			$where.= ' AND s."disciplin_type" = ' . $info['disciplin_id'];
		}
		if (!empty($info['class_name'])) {
			$where.= ' AND s."class_name" = \'' . $info['class_name'].'\'';
		}
		if (!empty($info['nojoinstatus'])) {
			if($info['nojoinstatus'] == 'Y'){
				$where.= ' AND s."exam_code" IS NOT NULL';
			}elseif($info['nojoinstatus'] == 'N'){
				$where.= ' AND s."exam_code" IS NULL';
			}else{
				$where.= ' AND s."statis" = \'' . $info['nojoinstatus'].'\'';
			}
		}
		
		
		#  科别的    #
		if(!empty($info['disciplin_code'])){
			$disciplin_data = ExamData::_getDisciplin($info['disciplin_code']);
			$where.= ' AND s."disciplin_name" = \''.$disciplin_data['disciplin_nick'].'\'';
		}
		
		if (!empty($info['subject_id'])) {
			
			$where.= (!empty($info['subject_id'])) ? ' AND P."subject_id" = ' . $info['subject_id'] :'';
			$sql = 'SELECT * FROM "{{student}}" S LEFT JOIN "{{point}}" P ON S."student_id"=P."student_id" AND S."exam_id" = P."exam_id" WHERE 1=1' . $where . $order;
			$count = 'SELECT COUNT(S."student_id") FROM "{{student}}" S LEFT JOIN "{{point}}" P ON S."student_id" = P."student_id" AND S."exam_id" = P."exam_id" WHERE 1=1 ' . $where;
		}else{
			$sql = 'SELECT * FROM "{{student}}" S WHERE 1=1' . $where . $order;
			$count = 'SELECT COUNT(S."student_id") FROM "{{student}}" S WHERE 1=1 ' . $where;
		}
        //echo $sql;exit;
		if (isset($info['import'])) {
			if ($info['import'] == '导出') {
				$this->import($sql);
			}
		}
        //echo $sql;exit;
		//$count = 'SELECT count(S."student_id") FROM "{{student}}" S LEFT JOIN "{{point}}" P ON S."student_id"=P."student_id" WHERE 1=1 ' . $where;

		$data = ZCommon::getDaoPageList($sql, $count, $limit);
		$pageUrl = $this->createUrl('/export/results/index' , $info);
		$this->render('index', array(
		'data' => $data['listDatas'],
		'pages' => $data['pages'],
		'pageUrl' => $pageUrl,
		'date' => $date,
		'info' => $info
		));
	}

	//导出
	public function import($sql) {
		$data = Yii::app()->db->createcommand($sql)->queryAll();
		 
		$zExam = new zExam();
		
		spl_autoload_unregister(array('YiiBase', 'autoload'));
		include_once(dirname(dirname(dirname(dirname(__FILE__)))) . '/extensions/PHPExcel.php');
		include_once(dirname(dirname(dirname(dirname(__FILE__)))) . '/extensions/PHPExcel/IOFactory.php');
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->setActiveSheetIndex(0)
		->setCellValue('A1', '学生id')
		->setCellValue('B1', '学生姓名')
		->setCellValue('C1', '考试项目')
		->setCellValue('D1', '准考号')
		->setCellValue('E1', '城市代码')
		->setCellValue('F1', '城市简称')
		->setCellValue('G1', '学校id')
		->setCellValue('H1', '学校代码')
		->setCellValue('I1', '学校名称')
		->setCellValue('J1', '班级名称')
		->setCellValue('K1', '学生性别')
		->setCellValue('L1', '中考成绩')
		->setCellValue('M1', '科别代码')
		->setCellValue('N1', '科别名称')
		->setCellValue('O1', '科别类型')
		->setCellValue('P1', '是否统计')
		->setCellValue('Q1', '学校性质')
		->setCellValue('R1', '学校星级')
		->setCellValue('S1', '学籍号')
		->setCellValue('T1', '备注')
		->setCellValue('U1', '语文客观分')
		->setCellValue('V1', '语文主观分')
		->setCellValue('W1', '语文作文分')
		->setCellValue('X1', '语文总分')
		->setCellValue('Y1', '语文附加分')
		->setCellValue('Z1', '数学填空')
		->setCellValue('AA1', '数学解答')
		->setCellValue('AB1', '数学总分')
		->setCellValue('AC1', '数学附加')
		->setCellValue('AD1', '英语客观分')
		->setCellValue('AE1', '英语主观分')
		->setCellValue('AF1', '英语作文分')
		->setCellValue('AG1', '英语总分')
		->setCellValue('AH1', '物理客观分')
		->setCellValue('AI1', '物理主观分')
		->setCellValue('AJ1', '物理总分')
		->setCellValue('AK1', '化学客观分')
		->setCellValue('AL1', '化学主观分')
		->setCellValue('AM1', '化学总分')
		->setCellValue('AN1', '生物客观分')
		->setCellValue('AO1', '生物主观分')
		->setCellValue('AP1', '生物总分')
		->setCellValue('AQ1', '历史客观分')
		->setCellValue('AR1', '历史主观分')
		->setCellValue('AS1', '历史总分')
		->setCellValue('AT1', '政治客观分')
		->setCellValue('AU1', '政治主观分')
		->setCellValue('AV1', '政治总分')
		->setCellValue('AW1', '地理客观分')
		->setCellValue('AX1', '地理主观分')
		->setCellValue('AY1', '地理总分')
		->setCellValue('AZ1', '语数外总分')
		->setCellValue('BA1', '语数外总分（含附加分）')
		->setCellValue('BB1', '添加时间')
		->setCellValue('BC1', '跟踪号')
		->setCellValue('BD1', '更新时间');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('Q')	->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AF')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AG')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AH')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AI')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AJ')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AK')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AL')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AM')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AN')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AO')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AP')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AQ')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AR')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AS')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AT')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AU')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AV')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AW')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AX')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AY')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('AZ')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('BA')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('BB')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('BC')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('BD')->setWidth(15);
		$i = 2;
		
		$exam  = $eArray = array();
		foreach ($data as $v) {
			$sex = '';
			if (isset($v['student_sex'])){
				if ($v['student_sex']==1){
					$sex='男';
				}
				if ($v['student_sex']==2){
					$sex='女';
				}
			}
			
			if(isset($eArray[$v['exam_id']])){
				if(!empty($eArray[$v['exam_id']])){
					$exam['exam_term_name'] = $eArray[$v['exam_id']]['exam_term_name'];
				}
			}else{
				$eArray[$v['exam_id']] = $zExam->_getExamInfo($v['exam_id']);
				if(!empty($eArray[$v['exam_id']])){
					$exam['exam_term_name'] = $eArray[$v['exam_id']]['exam_term_name'];
				}
			}
			
			$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A' . $i,  $v['student_id'])
			->setCellValue('B' . $i, $v['student_name'])
			->setCellValue('C' . $i, $exam['exam_term_name'])
			->setCellValue('D' . $i, $v['exam_code'])
			->setCellValue('E' . $i, $v['city_code'])
			->setCellValue('F' . $i, $v['city_name'])
			->setCellValue('G' . $i, $v['school_id'])
			->setCellValue('H' . $i, $v['school_code'])
			->setCellValue('I' . $i, $v['school_name'])
			->setCellValue('J' . $i, $v['class_name'])
			->setCellValue('K' . $i, $sex)
			->setCellValue('L' . $i, $v['socre'])
			->setCellValue('M' . $i, $v['disciplin_code'])
			->setCellValue('N' . $i, $v['disciplin_name'])
			->setCellValue('O' . $i, $v['disciplin_type'])
			->setCellValue('P' . $i, $v['default_statis'])
			->setCellValue('Q' . $i, $v['nature'])
			->setCellValue('R' . $i, $v['interstellar'])
			->setCellValue('S' . $i, ' '.$v['roll'])
			->setCellValue('T' . $i, $v['ramark'])
			->setCellValue('U' . $i, $v['ywkgf'])
			->setCellValue('V' . $i, $v['ywzgf'])
			->setCellValue('W' . $i, $v['ywzwf'])
			->setCellValue('X' . $i, $v['ywf'])
			->setCellValue('Y' . $i, $v['ywfjf'])
			->setCellValue('Z' . $i, $v['sxtkf'])
			->setCellValue('AA' . $i, $v['sxjdf'])
			->setCellValue('AB' . $i, $v['sxf'])
			->setCellValue('AC' . $i, $v['sxfjf'])
			->setCellValue('AD' . $i, $v['yykgf'])
			->setCellValue('AE' . $i, $v['yyzgf'])
			->setCellValue('AF' . $i, $v['yyzwf'])
			->setCellValue('AG' . $i, $v['yyf'])
			->setCellValue('AH' . $i, $v['wlkgf'])
			->setCellValue('AI' . $i, $v['wlzgf'])
			->setCellValue('AJ' . $i, $v['wlf'])
			->setCellValue('AK' . $i, $v['hxkgf'])
			->setCellValue('AL' . $i, $v['hxzgf'])
			->setCellValue('AM' . $i, $v['hxf'])
			->setCellValue('AN' . $i, $v['swkgf'])
			->setCellValue('AO' . $i, $v['swzgf'])
			->setCellValue('AP' . $i, $v['swf'])
			->setCellValue('AQ' . $i, $v['lskgf'])
			->setCellValue('AR' . $i, $v['lszgf'])
			->setCellValue('AS' . $i, $v['lsf'])
			->setCellValue('AT' . $i, $v['zzkgf'])
			->setCellValue('AU' . $i, $v['zzzgf'])
			->setCellValue('AV' . $i, $v['zzf'])
			->setCellValue('AW' . $i, $v['dlkgf'])
			->setCellValue('AX' . $i, $v['dlzgf'])
			->setCellValue('AY' . $i, $v['dlf'])
			->setCellValue('AZ' . $i, $v['yswzf'])
			->setCellValue('BA' . $i, $v['yswzfhfjf'])
			->setCellValue('BB' . $i, $v['track'])
			->setCellValue('BC' . $i, date('Y-m-d H:i',$v['addtime']));
			//->setCellValue('BD' . $i, date('Y-m-d H:i:s',$v['uptime']));
			$i++;
		}
		$objPHPExcel->setActiveSheetIndex(0);
		$file_nams = "园区学生成绩查询导出";
		$file_nams .= ".xls";
		if (preg_match("/MSIE/", $_SERVER["HTTP_USER_AGENT"])) {
			$file_nams = urlencode($file_nams);
			$file_nams = str_replace("+", "%20", $file_nams); // 替换空格
		}
		ob_end_clean();
		header("Content-Type: application/vnd.ms-excel;charset=utf-8");
		header("Content-Disposition: attachment; filename=" . $file_nams);
		header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
		header("Content-Type:application/download");
		header('Expires:0');
		header('Pragma:public');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		Yii::app()->end();
		spl_autoload_register(array('YiiBase', 'autoload'));
	}
	function actionGetexam(){
		$id = Yii::app()->request->getParam('id');
		$exam_where = ' WHERE "exam_id"='.$id;
		$exam_school = Nojoin::_getexam_schoolALL($exam_where);
		$exam_school_id = array();
		foreach ($exam_school as $k=>$v){
			$exam_school_id[] = $v['school_id'];
		}
		//得到学校信息
		$where = ' WHERE "school_id" IN ('.implode(",",$exam_school_id).')';
		$school_info = Nojoin::_getschoolAll($where);
		//得到科目信息
		$subject_where = ' AND "exam_id" ='.$id;
		$subjectinfo = ZTask::_getExamAll($subject_where);
		$subject_code = "'".str_replace("-","','",$subjectinfo[0]['exam_subject_ids'])."'";
		$sujectsql = ' SELECT * FROM "{{subject}}" WHERE "is_del"=0 AND "subject_code" IN ('.$subject_code.')';
		$subjectinfos = Yii::app()->db->createCommand($sujectsql)->queryAll();
		$data['school'] = $school_info;
		$data['suject'] = $subjectinfos;
		echo json_encode($data);
	}
	
}